--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
meshchatx/src/backend/database/schema.py 147e9451330dfa57b06aa877aa88941b226588af (147e9451) Text, 23.13 KB
Tff7b72from T7ee787.T7ee787provider Tff7b72import Te6edf3DatabaseProvider
Tff7b72class T56d364DatabaseSchemaTb4b4b4:
Te6edf3LATEST_VERSION Tff7b72= T79c0ff16
Tff7b72def Tff7b72__init__Tb4b4b4(Tff7b72selfTb4b4b4, Te6edf3providerTb4b4b4: Te6edf3DatabaseProviderTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffprovider Tff7b72= Te6edf3provider
Tff7b72def Td2a8ffinitializeTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
T8b949e# Create core tables if they don't exist
Tff7b72selfTff7b72.Td2a8ff_create_initial_tablesTb4b4b4(Tb4b4b4)
T8b949e# Run migrations
Te6edf3current_version Tff7b72= Tff7b72selfTff7b72.Td2a8ff_get_current_versionTb4b4b4(Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffmigrateTb4b4b4(Te6edf3current_versionTb4b4b4)
Tff7b72def Td2a8ff_get_current_versionTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT value FROM config WHERE key = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffdatabase_versionTa5d6ff"Tb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3rowTb4b4b4:
Tff7b72return Tffa657intTb4b4b4(Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffvalueTa5d6ff"Tb4b4b4]Tb4b4b4)
Tff7b72return T79c0ff0
Tff7b72def Td2a8ff_create_initial_tablesTb4b4b4(Tff7b72selfTb4b4b4)Tb4b4b4:
T8b949e# We create the config table first so we can track version
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS config (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff key TEXT UNIQUE,
Ta5d6ff value TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
T8b949e# Other essential tables that were present from version 1
T8b949e# Peewee automatically creates tables if they don't exist.
T8b949e# Here we define the full schema for all tables as they should be now.
Te6edf3tables Tff7b72= Tb4b4b4{
Ta5d6ff"Ta5d6ffannouncesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS announces (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff aspect TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff identity_public_key TEXT,
Ta5d6ff app_data TEXT,
Ta5d6ff rssi INTEGER,
Ta5d6ff snr REAL,
Ta5d6ff quality REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcustom_destination_display_namesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS custom_destination_display_names (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff display_name TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fffavourite_destinationsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS favourite_destinations (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff display_name TEXT,
Ta5d6ff aspect TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_messages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff hash TEXT UNIQUE,
Ta5d6ff source_hash TEXT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff state TEXT,
Ta5d6ff progress REAL,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff method TEXT,
Ta5d6ff delivery_attempts INTEGER DEFAULT 0,
Ta5d6ff next_delivery_attempt_at REAL,
Ta5d6ff title TEXT,
Ta5d6ff content TEXT,
Ta5d6ff fields TEXT,
Ta5d6ff timestamp REAL,
Ta5d6ff rssi INTEGER,
Ta5d6ff snr REAL,
Ta5d6ff quality REAL,
Ta5d6ff is_spam INTEGER DEFAULT 0,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_conversation_read_stateTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_conversation_read_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_read_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_user_iconsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_user_icons (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff icon_name TEXT,
Ta5d6ff foreground_colour TEXT,
Ta5d6ff background_colour TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffblocked_destinationsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS blocked_destinations (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffspam_keywordsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS spam_keywords (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff keyword TEXT UNIQUE,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffarchived_pagesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS archived_pages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff content TEXT,
Ta5d6ff hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcrawl_tasksTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS crawl_tasks (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff retry_count INTEGER DEFAULT 0,
Ta5d6ff last_retry_at DATETIME,
Ta5d6ff next_retry_at DATETIME,
Ta5d6ff status TEXT DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, page_path)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_forwarding_rulesTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_rules (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff forward_to_hash TEXT,
Ta5d6ff source_filter_hash TEXT,
Ta5d6ff is_active INTEGER DEFAULT 1,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_forwarding_mappingsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_mappings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff alias_identity_private_key TEXT,
Ta5d6ff alias_hash TEXT UNIQUE,
Ta5d6ff original_sender_hash TEXT,
Ta5d6ff final_recipient_hash TEXT,
Ta5d6ff original_destination_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffcall_historyTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS call_history (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff status TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffvoicemailsTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS voicemails (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff filename TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff is_read INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6ffnotification_viewed_stateTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_viewed_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_viewed_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Ta5d6ff"Ta5d6fflxmf_telemetryTa5d6ff"Tb4b4b4: Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_telemetry (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff timestamp REAL,
Ta5d6ff data BLOB,
Ta5d6ff received_from TEXT,
Ta5d6ff physical_link TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, timestamp)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4,
Tb4b4b4}
Tff7b72for Te6edf3table_nameTb4b4b4, Te6edf3create_sql Tff7b72in Te6edf3tablesTff7b72.Td2a8ffitemsTb4b4b4(Tb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3create_sqlTb4b4b4)
T8b949e# Create indexes that were present
Tff7b72if Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffannouncesTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_aspect ON announces(aspect)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_announces_identity_hash ON announces(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6fflxmf_messagesTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_source_hash ON lxmf_messages(source_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_messages_destination_hash ON lxmf_messages(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffblocked_destinationsTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_blocked_destinations_hash ON blocked_destinations(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffspam_keywordsTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_spam_keywords_keyword ON spam_keywords(keyword)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6ffnotification_viewed_stateTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notification_viewed_state_destination_hash ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_notification_viewed_state_dest_hash_unique ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elif Te6edf3table_name Tff7b72== Ta5d6ff"Ta5d6fflxmf_telemetryTa5d6ff"Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_destination_hash ON lxmf_telemetry(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_timestamp ON lxmf_telemetry(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_telemetry_dest_ts_unique ON lxmf_telemetry(destination_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffmigrateTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3current_versionTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff7Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS archived_pages (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff content TEXT,
Ta5d6ff hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_destination_hash ON archived_pages(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_page_path ON archived_pages(page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_archived_pages_hash ON archived_pages(hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff8Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS crawl_tasks (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff page_path TEXT,
Ta5d6ff retry_count INTEGER DEFAULT 0,
Ta5d6ff last_retry_at DATETIME,
Ta5d6ff next_retry_at DATETIME,
Ta5d6ff status TEXT DEFAULT Ta5d6ff'Ta5d6ffpendingTa5d6ff'Ta5d6ff,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_crawl_tasks_destination_hash ON crawl_tasks(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_crawl_tasks_page_path ON crawl_tasks(page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff9Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_rules (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff name TEXT,
Ta5d6ff identity_hash TEXT,
Ta5d6ff forward_to_hash TEXT,
Ta5d6ff source_filter_hash TEXT,
Ta5d6ff is_active INTEGER DEFAULT 1,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_rules_identity_hash ON lxmf_forwarding_rules(identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_forwarding_mappings (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff alias_identity_private_key TEXT,
Ta5d6ff alias_hash TEXT UNIQUE,
Ta5d6ff original_sender_hash TEXT,
Ta5d6ff final_recipient_hash TEXT,
Ta5d6ff original_destination_hash TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_alias_hash ON lxmf_forwarding_mappings(alias_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_sender_hash ON lxmf_forwarding_mappings(original_sender_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_recipient_hash ON lxmf_forwarding_mappings(final_recipient_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff10Tb4b4b4:
T8b949e# Ensure unique constraints exist for ON CONFLICT clauses
T8b949e# SQLite doesn't support adding UNIQUE constraints via ALTER TABLE,
T8b949e# but a UNIQUE index works for ON CONFLICT.
T8b949e# Clean up duplicates before adding unique indexes
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM announces WHERE id NOT IN (SELECT MAX(id) FROM announces GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM crawl_tasks WHERE id NOT IN (SELECT MAX(id) FROM crawl_tasks GROUP BY destination_hash, page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM custom_destination_display_names WHERE id NOT IN (SELECT MAX(id) FROM custom_destination_display_names GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM favourite_destinations WHERE id NOT IN (SELECT MAX(id) FROM favourite_destinations GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_user_icons WHERE id NOT IN (SELECT MAX(id) FROM lxmf_user_icons GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_conversation_read_state WHERE id NOT IN (SELECT MAX(id) FROM lxmf_conversation_read_state GROUP BY destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM lxmf_messages WHERE id NOT IN (SELECT MAX(id) FROM lxmf_messages GROUP BY hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_announces_destination_hash_unique ON announces(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_crawl_tasks_destination_path_unique ON crawl_tasks(destination_hash, page_path)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_custom_display_names_dest_hash_unique ON custom_destination_display_names(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_favourite_destinations_dest_hash_unique ON favourite_destinations(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_messages_hash_unique ON lxmf_messages(hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_user_icons_dest_hash_unique ON lxmf_user_icons(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_conversation_read_state_dest_hash_unique ON lxmf_conversation_read_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff11Tb4b4b4:
T8b949e# Add is_spam column to lxmf_messages if it doesn't exist
Tff7b72tryTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_messages ADD COLUMN is_spam INTEGER DEFAULT 0Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72except Tf85149ExceptionTb4b4b4:
T8b949e# Column might already exist if table was created with newest schema
Tff7b72pass
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff12Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS call_history (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff is_incoming INTEGER,
Ta5d6ff status TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_history_remote_hash ON call_history(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_call_history_timestamp ON call_history(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff13Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS voicemails (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff remote_identity_hash TEXT,
Ta5d6ff remote_identity_name TEXT,
Ta5d6ff filename TEXT,
Ta5d6ff duration_seconds INTEGER,
Ta5d6ff is_read INTEGER DEFAULT 0,
Ta5d6ff timestamp REAL,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_voicemails_remote_hash ON voicemails(remote_identity_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_voicemails_timestamp ON voicemails(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff14Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS notification_viewed_state (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT UNIQUE,
Ta5d6ff last_viewed_at DATETIME,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_notification_viewed_state_destination_hash ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_notification_viewed_state_dest_hash_unique ON notification_viewed_state(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff15Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"""
Ta5d6ff CREATE TABLE IF NOT EXISTS lxmf_telemetry (
Ta5d6ff id INTEGER PRIMARY KEY AUTOINCREMENT,
Ta5d6ff destination_hash TEXT,
Ta5d6ff timestamp REAL,
Ta5d6ff data BLOB,
Ta5d6ff received_from TEXT,
Ta5d6ff physical_link TEXT,
Ta5d6ff created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
Ta5d6ff UNIQUE(destination_hash, timestamp)
Ta5d6ff )
Ta5d6ff Ta5d6ff"""Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_destination_hash ON lxmf_telemetry(destination_hash)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE INDEX IF NOT EXISTS idx_lxmf_telemetry_timestamp ON lxmf_telemetry(timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffCREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_telemetry_dest_ts_unique ON lxmf_telemetry(destination_hash, timestamp)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72if Te6edf3current_version Tff7b72< T79c0ff16Tb4b4b4:
Tff7b72tryTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffALTER TABLE lxmf_forwarding_rules ADD COLUMN name TEXTTa5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72except Tf85149ExceptionTb4b4b4:
Tff7b72pass
T8b949e# Update version in config
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO config (key, value, created_at, updated_at)
VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
ON CONFLICT(key) DO UPDATE SET
value = EXCLUDED.value,
updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Ta5d6ff"Ta5d6ffdatabase_versionTa5d6ff"Tb4b4b4, Tffa657strTb4b4b4(Tff7b72selfTff7b72.Td2a8ffLATEST_VERSIONTb4b4b4)Tb4b4b4)Tb4b4b4,
Tb4b4b4)
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────